﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace DataBaseFactoryLib
{
    public abstract class BaseDataBaseOpr
    {
        public DataBaseTypeEnum DbType => dataBaseType;
        public string DBName => dataBaseName;
        public DbConnection Connection => conn;

        protected DataBaseTypeEnum dataBaseType;
        protected string server;
        protected int port;
        protected string uid;
        protected string pwd;
        protected string dataBaseName;
        protected DbConnection conn;

        public BaseDataBaseOpr()
        {
            SetDataBaseType();
            InstanceDBConn();
        }

        /// <summary>
        /// 设置数据库类型
        /// </summary>
        protected abstract void SetDataBaseType();

        /// <summary>
        /// 设置基础配置信息
        /// </summary>
        public void SetBaseParam(string serverName,int protNum,string uId,string password)
        {
            server = serverName;
            port = protNum;
            uid = uId;
            pwd = password;
        }

        /// <summary>
        /// 设置数据库名称
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        public void SetDataBaseName(string dbName)
        {
            dataBaseName = dbName;
            SetConnStr();
        }

        /// <summary>
        /// 初始化DbConnect类
        /// </summary>
        protected abstract void InstanceDBConn();

        /// <summary>
        /// 获取Connect字符串
        /// </summary>
        /// <returns></returns>
        protected abstract string GetConnStr();

        /// <summary>
        /// 设置Connect字符串
        /// </summary>
        protected virtual void SetConnStr()
        {
            conn.ConnectionString = GetConnStr();
        }

        public virtual void SetConnStr(string connStr)
        {
            conn.ConnectionString = connStr;
        }

        /// <summary>
        /// 初始化DbDataAdapter
        /// </summary>
        /// <returns></returns>
        protected abstract DbDataAdapter InstanceDbDataAdapter();

        /// <summary>
        /// 请求DataTable
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public DataTable QueryDataTable(string sqlStr)
        {
            try
            {
                conn.Open();
                DbDataAdapter sda = InstanceDbDataAdapter();
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                sda.SelectCommand = cmd;

                DataSet rtnDs = new DataSet();
                sda.Fill(rtnDs);
                var rtnDt = rtnDs.Tables[0].Copy();

                conn.Close();

                return rtnDt;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        public int ExecuteNonQuery(string sqlStr)
        {
            try
            {
                conn.Open();

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                cmd.CommandTimeout = 60;
                var rtnInt = cmd.ExecuteNonQuery();
                conn.Close();

                return rtnInt;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 返回查询首行首字段数据
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sqlStr)
        {
            try
            {
                conn.Open();

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                var scalar = cmd.ExecuteScalar();
                conn.Close();
                if (scalar == null || scalar == DBNull.Value)
                {
                    return default;
                }
                else
                {
                    return (T)scalar;
                }
            }
            catch { conn.Close(); throw; }
        }

        private DbDataReader GetDbDataReader(string sqlStr)
        {
            try
            {
                conn.Open();

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlStr;
                DbDataReader dr = cmd.ExecuteReader();

                return dr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 根据sql返回Lst
        /// </summary>
        /// <typeparam name="T">泛型类</typeparam>
        /// <param name="sqlStr">sql语句</param>
        /// <returns></returns>
        public List<T> FindBySql<T>(string sqlStr) where T : class, new()
        {
            DbDataReader dr = GetDbDataReader(sqlStr);

            Type t = typeof(T);
            List<T> tLst = new List<T>();
            PropertyInfo[] properties = t.GetProperties(BindingFlags.Instance | BindingFlags.Public);

            try
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        T tObj = new T();//创建一个实例
                        foreach (var item in properties)
                        {
                            Attribute nullAttr = item.GetCustomAttribute(typeof(ColumnAttribute));

                            if (nullAttr == null)
                            {
                                continue;
                            }

                            ColumnAttribute column = item.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;

                            if (column.Ignore)
                            {
                                continue;
                            }

                            if (!ReaderExists(dr, column.Name))
                            {
                                continue;
                            }

                            #region 根据类型为当前属性赋值

                            if (item.PropertyType == typeof(int))
                            {
                                //为当前属性赋值
                                //*注意此处可能存在装箱拆箱的问题
                                item.SetValue(tObj, Convert.ToInt32(dr[column.Name].ToString()));

                            }
                            else if (item.PropertyType == typeof(double))
                            {
                                item.SetValue(tObj, Convert.ToDouble(dr[column.Name].ToString()));
                            }
                            else if (item.PropertyType == typeof(string))
                            {
                                item.SetValue(tObj, dr[column.Name].ToString());
                            }
                            else if (item.PropertyType == typeof(DateTime))
                            {
                                item.SetValue(tObj, Convert.ToDateTime(dr[column.Name].ToString()));
                            }
                            else if (item.PropertyType == typeof(decimal))
                            {
                                item.SetValue(tObj, Convert.ToDecimal(dr[column.Name].ToString()));
                            }
                            else if (item.PropertyType == typeof(bool))
                            {
                                item.SetValue(tObj, Convert.ToBoolean(dr[column.Name].ToString()));
                            }
                            #endregion
                        }
                        tLst.Add(tObj);
                    }
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return tLst;
        }

        public List<T> FindByDataTable<T>(DataTable dt) where T : class, new()
        {
            Type t = typeof(T);
            List<T> tLst = new List<T>();
            PropertyInfo[] properties = t.GetProperties(BindingFlags.Instance | BindingFlags.Public);

            string error = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                T tObj = new T();//创建一个实例
                foreach (var item in properties)
                {
                    Attribute nullAttr = item.GetCustomAttribute(typeof(ColumnAttribute));

                    if (nullAttr == null)
                    {
                        continue;
                    }

                    ColumnAttribute column = item.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;

                    if (column.Ignore)
                    {
                        continue;
                    }

                    if (!dt.Columns.Contains(column.Name))
                    {
                        continue;
                    }

                    #region 根据类型为当前属性赋值

                    if (item.PropertyType == typeof(int))
                    {
                        //为当前属性赋值
                        //*注意此处可能存在装箱拆箱的问题
                        item.SetValue(tObj, Convert.ToInt32(dr[column.Name].ToString()));

                    }
                    else if (item.PropertyType == typeof(double))
                    {
                        item.SetValue(tObj, Convert.ToDouble(dr[column.Name].ToString()));
                    }
                    else if (item.PropertyType == typeof(string))
                    {
                        item.SetValue(tObj, dr[column.Name].ToString());
                    }
                    else if (item.PropertyType == typeof(DateTime))
                    {
                        item.SetValue(tObj, Convert.ToDateTime(dr[column.Name].ToString()));
                    }
                    else if (item.PropertyType == typeof(decimal))
                    {
                        item.SetValue(tObj, Convert.ToDecimal(dr[column.Name].ToString()));
                    }
                    else if (item.PropertyType == typeof(bool))
                    {
                        item.SetValue(tObj, Convert.ToBoolean(dr[column.Name].ToString()));
                    }
                    #endregion
                }
                tLst.Add(tObj);
            }
            return tLst;
        }

        public DataTable ListToDataTable<T>(List<T> lst)
        {
            var propertyInfoArray = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
            if (propertyInfoArray.Length <= 0)
            {
                return null;
            }

            var rtnDt = new DataTable();
            if (typeof(T).GetCustomAttribute(typeof(TableAttribute)) is TableAttribute tableAttr)
            {
                rtnDt.TableName = tableAttr.Name;
            }

            foreach (var item in propertyInfoArray)
            {

                Attribute nullAttr = item.GetCustomAttribute(typeof(ColumnAttribute));

                if (nullAttr == null)
                {
                    continue;
                }

                ColumnAttribute column = item.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;

                if (column.Ignore)
                {
                    continue;
                }

                rtnDt.Columns.Add(column.Name);
            }

            if (rtnDt.Columns.Count <= 0)
            {
                return null;
            }

            foreach (var item in lst)
            {
                var newDr = rtnDt.NewRow();

                foreach (var propertyInfo in propertyInfoArray)
                {

                    Attribute nullAttr = propertyInfo.GetCustomAttribute(typeof(ColumnAttribute));

                    if (nullAttr == null)
                    {
                        continue;
                    }

                    ColumnAttribute column = propertyInfo.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;

                    if (column.Ignore)
                    {
                        continue;
                    }

                    newDr[column.Name] = propertyInfo.GetValue(item).ToString();
                }

                rtnDt.Rows.Add(newDr);
            }

            return rtnDt;
        }

        private static bool ReaderExists(DbDataReader dr, string columnName)
        {
            int count = dr.FieldCount;
            for (int i = 0; i < count; i++)
            {
                if (dr.GetName(i).Equals(columnName))
                {
                    return true;
                }
            }
            return false;
        }

        public int Save<T>(T entity)
        {
            var tableAtrr = typeof(T).GetCustomAttribute<TableAttribute>();
            if (tableAtrr != null)
            {
                string sql = "insert into " + tableAtrr.Name;
                string columnStr = "(";
                string valueStr = "VALUES(";
                var propertyInfoArray = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
                if (propertyInfoArray.Any())
                {
                    foreach (var item in propertyInfoArray)
                    {
                        var columnAttr = item.GetCustomAttribute<ColumnAttribute>();
                        if (columnAttr == null || columnAttr.Ignore)
                        {
                            continue;
                        }
                        columnStr += columnAttr.Name + ",";
                        valueStr += "'" + item.GetValue(entity).ToString() + "',";
                    }

                    columnStr = columnStr[0..^1] + ")";
                    valueStr = valueStr[0..^1] + ")";

                    sql += columnStr + valueStr;

                    return ExecuteNonQuery(sql);
                }
                else
                {
                    throw new Exception("对象无可识别属性");
                }
            }
            else
            {
                throw new Exception("对象类无Table特性");
            }
        }

        public int Update<T>(T entity)
        {
            var tableAtrr = typeof(T).GetCustomAttribute<TableAttribute>();
            if (tableAtrr != null)
            {
                string sql = "update " + tableAtrr.Name;
                string valueStr = " set ";
                string conStr = " where 1 = 1";
                var propertyInfoArray = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
                if (propertyInfoArray.Any())
                {
                    foreach (var item in propertyInfoArray)
                    {
                        var columnAttr = item.GetCustomAttribute<ColumnAttribute>();
                        if (columnAttr == null || columnAttr.Ignore)
                        {

                            continue;
                        }
                        if (columnAttr.GetType() == typeof(IdAttribute))
                        {
                            conStr += " and " + columnAttr.Name + "='" + item.GetValue(entity).ToString() + "'";
                        }
                        valueStr += columnAttr.Name + "='" + item.GetValue(entity).ToString() + "',";
                    }

                    valueStr = valueStr[0..^1];

                    sql += valueStr + conStr;

                    return ExecuteNonQuery(sql);
                }
                else
                {
                    throw new Exception("对象无可识别属性");
                }
            }
            else
            {
                throw new Exception("对象类无Table特性");
            }
        }

        /// <summary>
        /// 测试连接
        /// </summary>
        /// <param name="errorStr">错误信息(out输出)</param>
        /// <returns></returns>
        public bool ConnectTest(out string errorStr)
        {
            try
            {
                conn.Open();
                conn.Close();
                errorStr = string.Empty;

                return true;
            }
            catch (Exception ex)
            {
                conn.Close();
                errorStr = ex.Message;

                return false;
            }
        }
    }
}
